/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.interfacce;

import Bean.MeasureData;
import Bean.Phenomenon;
import Bean.Vineyard;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Mimmo
 */
public class AgronomousDAO_DB implements AgronomousDAO {

    Connection connection;

    public AgronomousDAO_DB(String connectionString, String username, String password) throws Exception {
        connection = DriverManager.getConnection(connectionString, username, password);
    }

    public List<MeasureData> monitoringParameters(int v_id) {
        ArrayList<MeasureData> list = new ArrayList<MeasureData>();
        PreparedStatement ps;
        MeasureData m =null;
        try {

            ps = connection.prepareStatement("Call monitoring(?)");
            ps.setInt(1, v_id);
            ResultSet r = ps.executeQuery();
            
            while (r.next()) {
                m = new MeasureData();
                m.setDate(r.getDate("date"));
                m.setS_id(r.getInt("s-id"));
                m.setTime(r.getTime("time"));
                m.setValue(r.getDouble("value"));
                list.add(m);
            }
            

        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

       return list;
    }

    public List<MeasureData> monitoringParameters(int v_id, Date data_in, Date data_out) {

        ArrayList<MeasureData> list = new ArrayList<MeasureData>();
        PreparedStatement ps;
        try {
            ps = connection.prepareStatement("Call monitoring(?)");
            ps.setInt(1, v_id);
            ResultSet r = ps.executeQuery();
            while (r.next()) {
                MeasureData m = new MeasureData();
                m.setDate(r.getDate("date"));
                m.setS_id(r.getInt("s_id"));
                m.setTime(r.getTime("time"));
                m.setValue(r.getDouble("value"));

                list.add(m);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;


    }

    public Vineyard infoVineyard(int v_id) {
        Vineyard vineyard = new Vineyard();

        PreparedStatement ps;
        try {
            ps = connection.prepareStatement("Call infoVineyard(?)");
            ps.setInt(1, v_id);
            ResultSet r = ps.executeQuery();
            while (r.next()) {
                vineyard.setDistrict(r.getString("district"));
                vineyard.setFraction(r.getString("fraction"));
                vineyard.setHectares(r.getInt("hectares"));
                vineyard.setMunicipality(r.getString("municipality"));
                vineyard.setV_id(r.getInt("v-id"));
                vineyard.setZone(r.getString("zone"));

            }
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return vineyard;
    }

    public List<Vineyard> getAllVineyard() {

        ArrayList<Vineyard> list = new ArrayList<Vineyard>();


        try {

            Statement s = connection.createStatement();
            ResultSet r = s.executeQuery("Call getAllVineyard()");
            while (r.next()) {
                Vineyard vineyard = new Vineyard();
                vineyard.setDistrict(r.getString("district"));
                vineyard.setFraction(r.getString("fraction"));
                vineyard.setHectares(r.getInt("hectares"));
                vineyard.setMunicipality(r.getString("municipality"));
                vineyard.setV_id(r.getInt("v-id"));
                vineyard.setZone(r.getString("zone"));
                list.add(vineyard);

            }
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }

    public List<Phenomenon> getAllEvents() {
        ArrayList<Phenomenon> list = new ArrayList<Phenomenon>();
        Statement s;
        try {
            s = connection.createStatement();
            ResultSet r = s.executeQuery("Call getAllEvents()");
            while (r.next()) {
                Phenomenon p = new Phenomenon();
                p.setDate(r.getDate("date"));
                p.setEmergency_plan(r.getString("Emergency_plan"));
                p.setName(r.getString("name"));
                p.setNotes(r.getString("notes"));
                p.setType(r.getString("Type"));
                p.setV_id(r.getInt("v_id"));

            }
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;


    }

    public List<Phenomenon> monitoringEvents(int v_id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Phenomenon> monitoringEvents(int v_id, Date data_in, Date data_out) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void storeVineyard(Vineyard vineyard) {
        try {
            PreparedStatement ps = connection.prepareStatement("Call storeVineyard(?,?,'?','?','?','?','?')");
            ps.setInt(1, vineyard.getV_id());
            ps.setInt(2, vineyard.getHectares());
            ps.setString(3, vineyard.getFraction());
            ps.setString(4, vineyard.getMunicipality());
            ps.setString(5, vineyard.getDistrict());
            ps.setString(6, vineyard.getRegion());
            ps.setString(7, vineyard.getZone());
            ps.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void storeVineyard(Vineyard vineyard, List<MeasureData> sensorParameter) {
        try {
            PreparedStatement ps = connection.prepareStatement("Call storeVineyard(?,?,'?','?','?','?','?')");
            ps.setInt(1, vineyard.getV_id());
            ps.setInt(2, vineyard.getHectares());
            ps.setString(3, vineyard.getFraction());
            ps.setString(4, vineyard.getMunicipality());
            ps.setString(5, vineyard.getDistrict());
            ps.setString(6, vineyard.getRegion());
            ps.setString(7, vineyard.getZone());
            ps.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        Iterator<MeasureData> i = sensorParameter.iterator();

        while (i.hasNext()) {
            MeasureData m =  i.next();
            try {
                PreparedStatement ps = connection.prepareStatement("insert  into `measure-date`(`date`,`time`,`s-id`,`value`) values ('?','?',?,?)");
                ps.setDate(1, m.getDate());
                ps.setTime(2,m.getTime());
                ps.setInt(3, m.getS_id());
                ps.setDouble(4, m.getValue());
            } catch (SQLException ex) {
                Logger.getLogger(AgronomousDAO_DB.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    }


}
